#!/usr/bin/python
# -*- coding: UTF-8 -*-

#import MySQLdb
import time
import datetime
import mysql.connector
from  decimal import Decimal
from  decimal import getcontext

# 创建链接数据库
config = {'host': '127.0.0.1',  # 默认127.0.0.1
          'user': 'root', 'password': '654321', 'port': 3306,  # 默认即为3306
          'database': 'worm_data', 'charset': 'utf8'  # 默认即为utf8
          }

config1 = {'host': 'localhost',  # 默认127.0.0.1
          'user': 'root', 'password': '123456', 'port': 3306,  # 默认即为3306
          'database': 'shopxxb2b2c', 'charset': 'utf8'  # 默认即为utf8
          }

config3 = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }

#demical保留小数位数
getcontext().prec = 4




# 查询数据库列表
def findList(type, price_date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config3)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select * from province_avg_price where type = %s and price_date = %s order by id desc '
        cursor.execute(stmt, (type, price_date))
        data = cursor.fetchall()
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


# 更新每天省市的平均值
def insert_data(data):

    cnn = ''
    try:
        cnn = mysql.connector.connect(**config3)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'insert INTO everyday_avg_price(avg_price, type, product_name, price_date, created_date, last_modified_date, version) values (%s, %s, %s, %s, NOW(), NOW(), 0 )'
        cursor.execute(stmt, data)
        count = cursor.rowcount
        cnn.commit()
        print("保存数据成功！保存记录数", count)

    except mysql.connector.Error as e:
        print('保存处理数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    print(re_date)
    return re_date

def pork_avg(type, dayNum):
    # 前1天数据 基准数据
    data_1 = findList(pork_type, getdate(dayNum))
    data_1_copy = []
    data_1_copy.extend(data_1)

    avg_price = Decimal(0.00)
    shareCount = Decimal(0)
    for item in list(data_1_copy):

        province_name = item[5]
        price = Decimal(item[1])
        if '新疆' in province_name :
            avg_price += price * Decimal(0.34)/100
            shareCount += Decimal(0.34)
        elif '内蒙古' in province_name:
            avg_price += price * Decimal(1.54)/100
            shareCount += Decimal(1.54)
        elif '山东' in province_name:
            avg_price += price * Decimal(4.66)/100
            shareCount += Decimal(4.66)
        elif '河北' in province_name:
            avg_price += price * Decimal(3.31)/100
            shareCount += Decimal(3.31)
        elif '河南' in province_name:
            avg_price += price * Decimal(3.73)/100
            shareCount += Decimal(3.73)
        elif '广东' in province_name:
            avg_price += price * Decimal(11.6)/100
            shareCount += Decimal(11.6)
        elif '辽宁' in province_name:
            avg_price += price * Decimal(2.89)/100
            shareCount += Decimal(2.89)
        elif '江苏' in province_name:
            avg_price += price * Decimal(5.60)/100
            shareCount += Decimal(5.60)
        elif '甘肃' in province_name:
            avg_price += price * Decimal(1.07)/100
            shareCount += Decimal(1.07)
        elif '北京' in province_name:
            avg_price += price * Decimal(0.99)/100
            shareCount += Decimal(0.99)
        elif '安徽' in province_name:
            avg_price += price * Decimal(4.12)/100
            shareCount += Decimal(4.12)
        elif '黑龙江' in province_name:
            avg_price += price * Decimal(2.09)/100
            shareCount += Decimal(2.09)
        elif '天津' in province_name:
            avg_price += price * Decimal(0.88)/100
            shareCount += Decimal(0.88)
        elif '山西' in province_name:
            avg_price += price * Decimal(1.25)/100
            shareCount += Decimal(1.25)
        elif '湖南' in province_name:
            avg_price += price * Decimal(6.63)/100
            shareCount += Decimal(6.63)
        elif '陕西' in province_name:
            avg_price += price * Decimal(1.4)/100
            shareCount += Decimal(1.4)
        elif '青海' in province_name:
            avg_price += price * Decimal(0.24)/100
            shareCount += Decimal(0.24)
        elif '四川' in province_name:
            avg_price += price * Decimal(10.76)/100
            shareCount += Decimal(10.76)
        elif '广西' in province_name:
            avg_price += price * Decimal(4.83)/100
            shareCount += Decimal(4.83)
        elif '湖北' in province_name:
            avg_price += price * Decimal(4.37)/100
            shareCount += Decimal(4.37)
        elif '浙江' in province_name:
            avg_price += price * Decimal(4.43)/100
            shareCount += Decimal(4.43)
        elif '宁夏' in province_name:
            avg_price += price * Decimal(0.15)/100
            shareCount += Decimal(0.15)
        elif '福建' in province_name:
            avg_price += price * Decimal(3.63)/100
            shareCount += Decimal(3.63)
        elif '西藏' in province_name:
            avg_price += price * Decimal(0.09)/100
            shareCount += Decimal(0.09)
        elif '重庆' in province_name:
            avg_price += price * Decimal(3.73)/100
            shareCount += Decimal(3.73)
        elif '云南' in province_name:
            avg_price += price * Decimal(4.62)/100
            shareCount += Decimal(4.62)
        elif '上海' in province_name:
            avg_price += price * Decimal(1.69)/100
            shareCount += Decimal(1.69)
        elif '吉林' in province_name:
            avg_price += price * Decimal(1.53)/100
            shareCount += Decimal(1.53)
            print(province_name, shareCount)
        elif '江西' in province_name:
            avg_price += price * Decimal(3.41)/100
            shareCount += Decimal(3.41)
        elif '吉林' in province_name:
            avg_price += price * Decimal(1.53)/100
            shareCount += Decimal(1.53)
        elif '贵州' in province_name:
            avg_price += price * Decimal(3.54)/100
            shareCount += Decimal(3.54)
        elif '海南' in province_name:
            avg_price += price * Decimal(0.83)/100
            shareCount += Decimal(0.83)
        else:
            avg_price += price * Decimal(0.1)/100
            shareCount += Decimal(0.1)

    print('所占百分比', shareCount)
    # 平均值除以所占的百分比得到市场均价
    avg_price = avg_price / Decimal(shareCount) * 100
    # avg_price, type, product_name, price_date,
    result_data = [str(Decimal(avg_price)), type, str(list(data_1_copy[0])[4]), str(list(data_1_copy[0])[2])]
    print("猪肉全国平均价：", Decimal(avg_price), '比例值：', shareCount)
    if len(result_data) > 0:
        insert_data(result_data)



def beef_avg(type, dayNum):
    # 前1天数据 基准数据
    data_1 = findList(beef_type, getdate(dayNum))
    data_1_copy = []
    data_1_copy.extend(data_1)

    avg_price = Decimal(0.00)
    shareCount = Decimal(0)
    for item in list(data_1_copy):

        province_name = item[5]
        price = Decimal(item[1])
        if '新疆' in province_name:
            avg_price += price * Decimal(4.26) / 100
            shareCount += Decimal(4.26)
        elif '内蒙古' in province_name:
            avg_price += price * Decimal(4.12) / 100
            shareCount += Decimal(4.12)
        elif '山东' in province_name:
            avg_price += price * Decimal(4.55) / 100
            shareCount += Decimal(4.55)
        elif '河北' in province_name:
            avg_price += price * Decimal(3.99) / 100
            shareCount += Decimal(3.99)
        elif '河南' in province_name:
            avg_price += price * Decimal(4.34) / 100
            shareCount += Decimal(4.34)
        elif '广东' in province_name:
            avg_price += price * Decimal(8.46) / 100
            shareCount += Decimal(8.46)
        elif '辽宁' in province_name:
            avg_price += price * Decimal(4.3) / 100
            shareCount += Decimal(4.3)
        elif '江苏' in province_name:
            avg_price += price * Decimal(5.78) / 100
            shareCount += Decimal(5.78)
        elif '甘肃' in province_name:
            avg_price += price * Decimal(1.39) / 100
            shareCount += Decimal(1.39)
        elif '北京' in province_name:
            avg_price += price * Decimal(2.38) / 100
            shareCount += Decimal(2.38)
        elif '安徽' in province_name:
            avg_price += price * Decimal(4.5) / 100
            shareCount += Decimal(4.5)
        elif '黑龙江' in province_name:
            avg_price += price * Decimal(2.73) / 100
            shareCount += Decimal(2.73)
        elif '天津' in province_name:
            avg_price += price * Decimal(1.77) / 100
            shareCount += Decimal(1.77)
        elif '山西' in province_name:
            avg_price += price * Decimal(1.12) / 100
            shareCount += Decimal(1.12)
        elif '湖南' in province_name:
            avg_price += price * Decimal(4.93) / 100
            shareCount += Decimal(4.93)
        elif '陕西' in province_name:
            avg_price += price * Decimal(1.02) / 100
            shareCount += Decimal(1.02)
        elif '青海' in province_name:
            avg_price += price * Decimal(1.2) / 100
            shareCount += Decimal(1.2)
        elif '四川' in province_name:
            avg_price += price * Decimal(4.71) / 100
            shareCount += Decimal(4.71)
        elif '广西' in province_name:
            avg_price += price * Decimal(3.33) / 100
            shareCount += Decimal(3.33)
        elif '湖北' in province_name:
            avg_price += price * Decimal(3.8) / 100
            shareCount += Decimal(3.8)
        elif '浙江' in province_name:
            avg_price += price * Decimal(4.28) / 100
            shareCount += Decimal(4.28)
        elif '宁夏' in province_name:
            avg_price += price * Decimal(0.93) / 100
            shareCount += Decimal(0.93)
        elif '福建' in province_name:
            avg_price += price * Decimal(2.81) / 100
            shareCount += Decimal(2.81)
        elif '西藏' in province_name:
            avg_price += price * Decimal(1.77) / 100
            shareCount += Decimal(1.77)
        elif '重庆' in province_name:
            avg_price += price * Decimal(1.63) / 100
            shareCount += Decimal(1.63)
        elif '云南' in province_name:
            avg_price += price * Decimal(3.09) / 100
            shareCount += Decimal(3.09)
        elif '上海' in province_name:
            avg_price += price * Decimal(2.65) / 100
            shareCount += Decimal(2.65)
        elif '吉林' in province_name:
            avg_price += price * Decimal(2.26) / 100
            shareCount += Decimal(2.26)
            print(province_name, shareCount)
        elif '江西' in province_name:
            avg_price += price * Decimal(3.67) / 100
            shareCount += Decimal(3.67)
        elif '贵州' in province_name:
            avg_price += price * Decimal(1.63) / 100
            shareCount += Decimal(1.63)
        elif '海南' in province_name:
            avg_price += price * Decimal(0.63) / 100
            shareCount += Decimal(0.63)
        else:
            avg_price += price * Decimal(0.1) / 100
            shareCount += Decimal(0.1)

    # 平均值除以所占的百分比得到市场均价
    avg_price = avg_price / Decimal(shareCount) * 100
    # avg_price, type, product_name, price_date,
    result_data = [str(Decimal(avg_price)), type, str(list(data_1_copy[0])[4]), str(list(data_1_copy[0])[2])]
    print("牛肉全国平均价：", Decimal(avg_price), '比例值：', shareCount)
    if len(result_data) > 0:
        insert_data(result_data)

def mutton_avg(type, dayNum):
    # 前1天数据 基准数据
    data_1 = findList(mutton_type, getdate(dayNum))
    data_1_copy = []
    data_1_copy.extend(data_1)

    avg_price = Decimal(0.00)
    shareCount = Decimal(0)
    for item in list(data_1_copy):

        province_name = item[5]
        price = Decimal(item[1])
        if '新疆' in province_name:
            avg_price += price * Decimal(15.83) / 100
            shareCount += Decimal(15.83)
        elif '内蒙古' in province_name:
            avg_price += price * Decimal(12.46) / 100
            shareCount += Decimal(12.46)
        elif '山东' in province_name:
            avg_price += price * Decimal(6.09) / 100
            shareCount += Decimal(6.09)
        elif '河北' in province_name:
            avg_price += price * Decimal(5.83) / 100
            shareCount += Decimal(5.83)
        elif '河南' in province_name:
            avg_price += price * Decimal(5.82) / 100
            shareCount += Decimal(5.82)
        elif '广东' in province_name:
            avg_price += price * Decimal(4.33) / 100
            shareCount += Decimal(4.33)
        elif '辽宁' in province_name:
            avg_price += price * Decimal(3.63) / 100
            shareCount += Decimal(3.63)
        elif '江苏' in province_name:
            avg_price += price * Decimal(3.55) / 100
            shareCount += Decimal(3.55)
        elif '甘肃' in province_name:
            avg_price += price * Decimal(3.49) / 100
            shareCount += Decimal(3.49)
        elif '北京' in province_name:
            avg_price += price * Decimal(3.36) / 100
            shareCount += Decimal(3.36)
        elif '安徽' in province_name:
            avg_price += price * Decimal(3.12) / 100
            shareCount += Decimal(3.12)
        elif '黑龙江' in province_name:
            avg_price += price * Decimal(2.94) / 100
            shareCount += Decimal(2.94)
        elif '天津' in province_name:
            avg_price += price * Decimal(2.76) / 100
            shareCount += Decimal(2.76)
        elif '山西' in province_name:
            avg_price += price * Decimal(2.66) / 100
            shareCount += Decimal(2.66)
        elif '湖南' in province_name:
            avg_price += price * Decimal(2.66) / 100
            shareCount += Decimal(2.66)
        elif '陕西' in province_name:
            avg_price += price * Decimal(2.55) / 100
            shareCount += Decimal(2.55)
        elif '青海' in province_name:
            avg_price += price * Decimal(2.35) / 100
            shareCount += Decimal(2.35)
        elif '四川' in province_name:
            avg_price += price * Decimal(2.3) / 100
            shareCount += Decimal(2.3)
        elif '广西' in province_name:
            avg_price += price * Decimal(2.16) / 100
            shareCount += Decimal(2.16)
        elif '湖北' in province_name:
            avg_price += price * Decimal(1.96) / 100
            shareCount += Decimal(1.96)
        elif '浙江' in province_name:
            avg_price += price * Decimal(1.88) / 100
            shareCount += Decimal(1.88)
        elif '宁夏' in province_name:
            avg_price += price * Decimal(1.77) / 100
            shareCount += Decimal(1.77)
        elif '福建' in province_name:
            avg_price += price * Decimal(1.73) / 100
            shareCount += Decimal(1.73)
        elif '西藏' in province_name:
            avg_price += price * Decimal(1.47) / 100
            shareCount += Decimal(1.47)
        elif '重庆' in province_name:
            avg_price += price * Decimal(1.36) / 100
            shareCount += Decimal(1.36)
        elif '云南' in province_name:
            avg_price += price * Decimal(1.33) / 100
            shareCount += Decimal(1.33)
        elif '上海' in province_name:
            avg_price += price * Decimal(1.20) / 100
            shareCount += Decimal(1.20)
        elif '吉林' in province_name:
            avg_price += price * Decimal(1.20) / 100
            shareCount += Decimal(1.20)
            print(province_name, shareCount)
        elif '江西' in province_name:
            avg_price += price * Decimal(1.02) / 100
            shareCount += Decimal(1.02)
        elif '贵州' in province_name:
            avg_price += price * Decimal(0.59) / 100
            shareCount += Decimal(0.59)
        elif '海南' in province_name:
            avg_price += price * Decimal(0.26) / 100
            shareCount += Decimal(0.26)
        else:
            avg_price += price * Decimal(0.1) / 100
            shareCount += Decimal(0.1)

    # 平均值除以所占的百分比得到市场均价
    avg_price = avg_price / Decimal(shareCount) * 100
    # avg_price, type, product_name, price_date,
    result_data = [str(Decimal(avg_price)), type, str(list(data_1_copy[0])[4]), str(list(data_1_copy[0])[2])]
    print("羊肉全国平均价：", Decimal(avg_price), '比例值：', shareCount)
    if len(result_data) > 0:
        insert_data(result_data)



def fish_avg(type, dayNum):
    # 前1天数据 基准数据
    data_1 = findList(fish_type, getdate(dayNum))
    data_1_copy = []
    data_1_copy.extend(data_1)

    avg_price = Decimal(0.00)
    shareCount = Decimal(0)
    for item in list(data_1_copy):

        province_name = item[5]
        price = Decimal(item[1])
        if '新疆' in province_name:
            avg_price += price * Decimal(0.50) / 100
            shareCount += Decimal(0.50)
        elif '内蒙古' in province_name:
            avg_price += price * Decimal(0.78) / 100
            shareCount += Decimal(0.78)
        elif '山东' in province_name:
            avg_price += price * Decimal(7.26) / 100
            shareCount += Decimal(7.26)
        elif '河北' in province_name:
            avg_price += price * Decimal(2.68) / 100
            shareCount += Decimal(2.68)
        elif '河南' in province_name:
            avg_price += price * Decimal(2.27) / 100
            shareCount += Decimal(2.27)
        elif '广东' in province_name:
            avg_price += price * Decimal(15.93) / 100
            shareCount += Decimal(15.93)
        elif '辽宁' in province_name:
            avg_price += price * Decimal(3.96) / 100
            shareCount += Decimal(3.96)
        elif '江苏' in province_name:
            avg_price += price * Decimal(9.09) / 100
            shareCount += Decimal(9.09)
        elif '甘肃' in province_name:
            avg_price += price * Decimal(0.36) / 100
            shareCount += Decimal(0.36)
        elif '北京' in province_name:
            avg_price += price * Decimal(1.17) / 100
            shareCount += Decimal(1.17)
        elif '安徽' in province_name:
            avg_price += price * Decimal(4.23) / 100
            shareCount += Decimal(4.23)
        elif '黑龙江' in province_name:
            avg_price += price * Decimal(1.94) / 100
            shareCount += Decimal(1.94)
        elif '天津' in province_name:
            avg_price += price * Decimal(1.65) / 100
            shareCount += Decimal(1.65)
        elif '山西' in province_name:
            avg_price += price * Decimal(0.56) / 100
            shareCount += Decimal(0.56)
        elif '湖南' in province_name:
            avg_price += price * Decimal(5.06) / 100
            shareCount += Decimal(5.06)
        elif '陕西' in province_name:
            avg_price += price * Decimal(0.60) / 100
            shareCount += Decimal(0.60)
        elif '青海' in province_name:
            avg_price += price * Decimal(0.07) / 100
            shareCount += Decimal(0.07)
        elif '四川' in province_name:
            avg_price += price * Decimal(3.95) / 100
            shareCount += Decimal(3.95)
        elif '广西' in province_name:
            avg_price += price * Decimal(3.24) / 100
            shareCount += Decimal(3.24)
        elif '湖北' in province_name:
            avg_price += price * Decimal(5.57) / 100
            shareCount += Decimal(5.57)
        elif '浙江' in province_name:
            avg_price += price * Decimal(8.39) / 100
            shareCount += Decimal(8.39)
        elif '宁夏' in province_name:
            avg_price += price * Decimal(0.11) / 100
            shareCount += Decimal(0.11)
        elif '福建' in province_name:
            avg_price += price * Decimal(6.31) / 100
            shareCount += Decimal(6.31)
        elif '西藏' in province_name:
            avg_price += price * Decimal(0.01) / 100
            shareCount += Decimal(0.01)
        elif '重庆' in province_name:
            avg_price += price * Decimal(2.02) / 100
            shareCount += Decimal(2.02)
        elif '云南' in province_name:
            avg_price += price * Decimal(1.11) / 100
            shareCount += Decimal(1.11)
        elif '上海' in province_name:
            avg_price += price * Decimal(3.80) / 100
            shareCount += Decimal(3.80)
        elif '吉林' in province_name:
            avg_price += price * Decimal(1.24) / 100
            shareCount += Decimal(1.24)
            print(province_name, shareCount)
        elif '江西' in province_name:
            avg_price += price * Decimal(3.64) / 100
            shareCount += Decimal(3.64)
        elif '贵州' in province_name:
            avg_price += price * Decimal(0.54) / 100
            shareCount += Decimal(0.54)
        elif '海南' in province_name:
            avg_price += price * Decimal(1.48) / 100
            shareCount += Decimal(1.48)
        else:
            avg_price += price * Decimal(0.1) / 100
            shareCount += Decimal(0.1)

    # 平均值除以所占的百分比得到市场均价
    avg_price = avg_price / Decimal(shareCount) * 100
    # avg_price, type, product_name, price_date,
    result_data = [str(Decimal(avg_price)), type, str(list(data_1_copy[0])[4]), str(list(data_1_copy[0])[2])]
    print("鱼全国平均价：", Decimal(avg_price), '比例值：', shareCount)
    if len(result_data) > 0:
        insert_data(result_data)


pork_type = 1
beef_type = 2
mutton_type = 3
fish_type = 4001

if __name__ == '__main__':
    # test()
    # nums = 6
    # for num in range(nums):
    #     pork_avg(pork_type, num+1)
    #     beef_avg(beef_type, num+1)
    #     mutton_avg(mutton_type, num+1)
    #     fish_avg(fish_type, num+1)
    # 昨天是1  前天是2 依次类推 每次只处理1天数据
    num = 1
    pork_avg(pork_type, num)
    beef_avg(beef_type, num)
    mutton_avg(mutton_type, num)
    fish_avg(fish_type, num)

        
        
